﻿#include <iostream>
#include <vector>
#include <tuple>
#include "Database.h"
#include "command.h"
#include "320AI.h"

void printTable(Table* table) {
    std::vector<size_t> allRowIDs = table->getAllRowIDs();
    std::vector<std::string> allColNames = table->getAllColumnNames();
    std::vector<std::vector<Value>> allValues = table->getValues(allRowIDs, allColNames);
    printValues(allValues);
    std::cout << std::endl;
}


int main() {
    std::cout << "测试Bot交互" << std::endl;
    ChatBot bot = ChatBot();  //这个字符串传递部分在Qt前端中完成，这里只是展示用法
    bot.initBot("MyDatabase1");  //记得要初始化
    std::cout << bot.chatWithUserInput("讲一讲students表的内容") << std::endl;
    
    std::cout << "\n切换到MyDatabase2" << std::endl;
    bot.initBot("MyDatabase2");  //重置bot历史记录和数据库信息

    std::cout << bot.chatWithUserInput("讲一讲courses表的内容") << std::endl;


    std::string command{};
    Database::createDatabase("testdb1");
    Database db = Database::loadFromFile("testdb1");
    Command a{ db };
    while (true) {
        command = a.readMultilineCommand();
        if (command == "QUIT;" || command == "quit;") {
            break;
        }
        a.executeCommand(command);
    }



    try {
        // 测试创建新数据库 MyDatabase1
        std::cout << "Creating a new database MyDatabase1..." << std::endl;
        bool createResult1 = Database::createDatabase("MyDatabase1");
        std::cout << "MyDatabase1 created: " << std::boolalpha << createResult1 << std::endl;

        // 加载数据库 MyDatabase1
        std::cout << "\nLoading MyDatabase1..." << std::endl;
        Database db1 = Database::loadFromFile("MyDatabase1");

        db1.logUserCommand("testLog1");

        // 创建 Students 表
        std::vector<std::tuple<std::string, std::string, bool, bool, bool, std::string, std::string, std::string, std::string>> studentsColumnDefinitions = {
            {"id", "INT", false, true, true, "", "", "", ""},
            {"name", "STRING", false, true, false, "unnamed", "", "", "20"},
            {"age", "INT", false, false, false, "18", "0", "120", ""},
            {"score", "DOUBLE", true, false, false, "", "0.0", "100.0", ""}
        };
        db1.createTable("Students", studentsColumnDefinitions, "id");

        // 创建 Fruit 表
        std::vector<std::tuple<std::string, std::string, bool, bool, bool, std::string, std::string, std::string, std::string>> fruitDefinitions = {
            {"fID", "INT", false, true, true, "", "", "", ""},
            {"fName", "STRING", false, true, false, "unnamed", "", "", "20"},
            {"fNum", "INT", false, false, false, "0", "0", "120", ""},
            {"fPrice", "DOUBLE", true, false, false, "", "0.0", "100.0", ""}
        };
        db1.createTable("FruitInfo", fruitDefinitions, "fID");

        Table* fruitTable = db1.getTable("FruitInfo");
        fruitTable->insertRowFromStrings({ "fID", "fName", "fNum", "fPrice" }, { "1", "Apple", "", "2.87" });
        fruitTable->insertRowFromStrings({ "fID", "fName", "fNum", "fPrice" }, { "2", "Banana", "3", "1.7" });
        fruitTable->insertRowFromStrings({ "fID", "fName", "fNum", "fPrice" }, { "3", "Mango", "99", "9.287" });

        // 获取 Students 表对象
        Table* studentsTable = db1.getTable("Students");
        std::cout << studentsTable->getTableStructure() << std::endl;
        printTable(studentsTable);

        // 测试添加列
        std::cout << "Adding a new column 'gender' to Students table..." << std::endl;
        bool addColumnResult = studentsTable->addColumn("gender", "STRING", true, false, false, "", "", "", "10");
        std::cout << "Column added: " << std::boolalpha << addColumnResult << std::endl;
        printTable(studentsTable);

        // 测试修改列
        std::cout << "Modifying column 'age' in Students table..." << std::endl;
        bool modifyColumnResult = studentsTable->modifyColumn("age", false, false, false, "20", "1", "100", "");
        std::cout << "Column modified: " << std::boolalpha << modifyColumnResult << std::endl;
        printTable(studentsTable);

        // 插入数据到 Students 表
        std::cout << "Inserting rows into Students table:" << std::endl;
        std::cout << "Insert row 1: " << studentsTable->insertRowFromStrings({ "id", "name", "age", "score", "gender" }, { "1", "Alice", "20", "85.5", "female" }) << std::endl;
        std::cout << "Insert row 2: " << studentsTable->insertRowFromStrings({ "id", "name", "age", "score", "gender" }, { "2", "Bob", "", "92", "male" }) << std::endl;
        std::cout << "Insert row 3: " << studentsTable->insertRowFromStrings({ "id", "name", "age", "score", "gender" }, { "3", "Charlie", "21", "88", "male" }) << std::endl;
        std::cout << "Insert row 3: " << studentsTable->insertRowFromStrings({ "id", "name", "age", "score", "gender" }, { "4", "测试JOIN", "21", "98", "male" }) << std::endl;
        printTable(studentsTable);

        // 保存 Students 表到文件
        std::cout << "Saving Students table to file..." << std::endl;
        bool saveResult = db1.saveTableToFile("Students");
        std::cout << "Students table saved: " << std::boolalpha << saveResult << std::endl;

        // 测试删除列
        std::cout << "Deleting column 'gender' from Students table..." << std::endl;
        bool deleteColumnResult = studentsTable->deleteColumns({ "gender" });
        std::cout << "Column deleted: " << std::boolalpha << deleteColumnResult << std::endl;
        printTable(studentsTable);

        // 测试获取指定行的值
        std::cout << "获取学生表中所有成绩在90以上的同学信息:" << std::endl;
        std::vector<size_t> rowIDs = studentsTable->getRowIDsByRange("score", "90", "");
        std::vector<std::string> colNames = { "id", "name", "age", "score" };
        std::vector<std::vector<Value>> values = studentsTable->getValues(rowIDs, colNames);
        printValues(values);

        // 测试删除行
        std::cout << "Deleting row 2 from Students table..." << std::endl;
        bool deleteRowResult = studentsTable->deleteRows({ 2 });
        std::cout << "Row deleted: " << std::boolalpha << deleteRowResult << std::endl;
        printTable(studentsTable);

        // 测试更新行
        std::cout << "更新Charlie的成绩..." << std::endl;
        bool updateRowResult = studentsTable->updateRows(studentsTable->getRowIDsByEqual("name", "Charlie"), { "score" }, { "95" });
        std::cout << "Row updated: " << std::boolalpha << updateRowResult << std::endl;
        printTable(studentsTable);

        db1.logUserCommand("testLog2");

        // 保存数据库 MyDatabase1
        std::cout << "Saving MyDatabase1..." << std::endl;
        db1.saveToFile();

        // 测试创建新数据库 MyDatabase2
        std::cout << "Creating a new database MyDatabase2..." << std::endl;
        bool createResult2 = Database::createDatabase("MyDatabase2");
        std::cout << "MyDatabase2 created: " << std::boolalpha << createResult2 << std::endl;

        // 加载数据库 MyDatabase2
        std::cout << "Loading MyDatabase2..." << std::endl;
        Database db2 = Database::loadFromFile("MyDatabase2");

        // 创建 Courses 表
        std::vector<std::tuple<std::string, std::string, bool, bool, bool, std::string, std::string, std::string, std::string>> coursesColumnDefinitions = {
            {"id", "INT", false, true, true, "", "", "", ""},
            {"name", "STRING", false, true, false, "unnamed", "", "", "50"},
            {"teacher", "STRING", true, false, false, "", "", "", "50"},
            {"credit", "DOUBLE", false, false, false, "3.0", "1.0", "5.0", ""}
        };
        db2.createTable("Courses", coursesColumnDefinitions, "id");

        // 获取 Courses 表对象
        Table* coursesTable = db2.getTable("Courses");
        printTable(coursesTable);

        // 插入数据到 Courses 表
        std::cout << "Inserting rows into Courses table:" << std::endl;
        std::cout << "Insert row 1: " << coursesTable->insertRowFromStrings({ "id", "name", "teacher", "credit" }, { "1", "Math", "Dr. Smith", "4.0" }) << std::endl;
        std::cout << "Insert row 2: " << coursesTable->insertRowFromStrings({ "id", "name", "teacher", "credit" }, { "2", "English", "Prof. Johnson", "3.5" }) << std::endl;
        std::cout << "Insert row 3: " << coursesTable->insertRowFromStrings({ "id", "name", "teacher", "credit" }, { "3", "History", "Dr. Williams", "4.5" }) << std::endl;
        std::cout << "Insert row 3: " << coursesTable->insertRowFromStrings({ "id", "name", "teacher", "credit" }, { "4", "CS", "测试JOIN", "4.5" }) << std::endl;
        printTable(coursesTable);

        // 保存数据库 MyDatabase2
        std::cout << "Saving MyDatabase2..." << std::endl;
        db2.saveToFile();

        /*

        // 测试删除数据库 MyDatabase1
        std::cout << "Dropping database MyDatabase1..." << std::endl;
        bool dropResult = Database::dropDatabase("MyDatabase1");
        std::cout << "MyDatabase1 dropped: " << std::boolalpha << dropResult << std::endl;

        // 测试加载已删除的数据库 MyDatabase1
        std::cout << "Trying to load the dropped database MyDatabase1..." << std::endl;
        try {
            Database db1Dropped = Database::loadFromFile("MyDatabase1");
        }
        catch (const std::exception& e) {
            std::cout << "Caught exception: " << e.what() << std::endl;
        }

        */

        // 测试加载数据库 MyDatabase2
        std::cout << "Loading MyDatabase2 after dropping MyDatabase1..." << std::endl;
        Database db2Loaded = Database::loadFromFile("MyDatabase2");

        // 获取加载的 Courses 表对象
        Table* coursesTableLoaded = db2Loaded.getTable("Courses");
        printTable(coursesTableLoaded);

        // 测试 join 函数
        std::cout << "Testing join function..." << std::endl;

        // 加载 MyDatabase1
        std::cout << "Loading MyDatabase1..." << std::endl;
        Database db1Loaded = Database::loadFromFile("MyDatabase1");

        // 获取 Students 表对象
        Table* studentsTableLoaded = db1Loaded.getTable("Students");

        // 执行 join 操作
        std::cout << "Joining Students and Courses tables on id column..." << std::endl;
        Table joinedTable = studentsTableLoaded->join(coursesTableLoaded, "Studemts_name", "Courses_teacher");

        // 打印 join 后的表结构
        std::cout << "Joined table structure:" << std::endl;
        std::cout << joinedTable.getTableStructure() << std::endl;

        // 打印 join 后的表数据
        std::cout << "Joined table data:" << std::endl;
        printTable(&joinedTable);

        // 测试在 join 后的表上执行查询
        std::cout << "Querying the joined table for students with credit > 4..." << std::endl;
        rowIDs = joinedTable.getRowIDsByRange("Courses_credit", "4", "");
        colNames = { "id", "name", "age", "score" };
        values = studentsTableLoaded->getValues(rowIDs, colNames);
        printValues(values);

        //测试log对于多行的处理
        db1.logUserCommand("CREATE TABLE users(\n    id INT PRIMARY KEY, \n    name VARCHAR(50), \n    email VARCHAR(100)\n); ");
        std::cout << db1.loadUserLog() << std::endl;
    }
    catch (const std::exception& e) {
        std::cout << "Caught exception: " << e.what() << std::endl;
    }

    return 0;
}